package ca.radiologydb.util;

import java.io.IOException;
import java.sql.*;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

/**
 * Servlet implementation class DataAnalysis
 * @author jeching
 */
public class DataAnalysis extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public DataAnalysis() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		HttpSession session = request.getSession();
		ServletOutputStream out = response.getOutputStream();
		Template template = new Template(this.getServletContext().getRealPath("/").toString());
		out.println(template.getTemplateTop());
		
		//Check permissions -> should be administrator. Deny access otherwise.
		if (session.getAttribute("username") == null
				|| session.getAttribute("usertype") == null) {
			out.println("Cannot display results as you're not logged in, or do not have the proper permissions.");
			out.println("<a href='login.jsp'>Login</a>");
		} else if (!session.getAttribute("usertype").equals("a")) {
			out.println("Cannot display results as you're not logged in, or do not have the proper permissions.");
			out.println("<a href='login.jsp'>Login</a>");

		} else {
			
			ConnectionManager manager = new ConnectionManager();
			Connection conn = manager.getConnection();
			
			//Get report parameters
			String name = (String) request.getParameter("patient_name");
			String date = (String) request.getParameter("test_date");
			if(date != null && date.trim().isEmpty())
				date = "on";
			String type = (String) request.getParameter("test_type");
			String granularity = (String) request.getParameter("date_granularity");
			Statement stmt = null;
			ResultSet rset = null;
			
			// If admin has not defined any report criteria, just return the overall number of records.
			if(!(name == null && date == null && type == null)) {
			
				//SQL query uses group by cube, and searches all criteria. Criteria specified by the user will be displayed from information superset.
				String sql = "SELECT COUNT(*),patient_name,test_type,TRUNC(test_date,'"+granularity+"') FROM radiology_record ";
				if(name != null)
				if(!name.equals("on")) {
					sql += "WHERE patient_name='"+name+"' ";
				}
				if(type != null)
				if(!type.equals("on")) {
					if(!name.equals("on"))
						sql += "AND ";
					else
						sql += "WHERE ";
					sql += "test_type='"+type+"' ";
				}
				if (date != null)
				if(!date.equals("on")) {
					if(!name.equals("on") || !type.equals("on"))
						sql += "AND ";
					else if(!name.equals("on") && !type.equals("on"))
						sql += "WHERE ";
					sql += "TRUNC(test_date,'"+granularity+"')=TRUNC(TO_DATE('"+date+":12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'),'"+granularity+"') ";
				}
				
				
				sql += "GROUP BY CUBE (patient_name,test_type,TRUNC(test_date,'"+granularity+"'))";
				try {
					stmt = conn.createStatement();
					rset = stmt.executeQuery(sql);
				}
				catch (Exception ex) {
					System.out.println("<hr>" + ex.getMessage() + "<hr>");
				}
				
				
				
				out.println("<table><tr>");
				
				//Build table according to how the report parameters are defined.
				if(name != null) {
					out.println("<th>Patient Username</th>");
				}
				if(type != null) {
					out.println("<th>Test Type</th>");
				}
				if(date != null) {
					out.println("<th>Time Period Start</th>");
				}
				
				out.println("<th>Records</th></tr>");
				
				
				try {
					//Iterate through the results.
					while(rset != null && rset.next()) {
						
						String r_name = rset.getString(2);
						String r_type = rset.getString(3);
						String r_date = rset.getString(4);
						String count = rset.getString(1);
						
						//Next if desired property is not contained in row
						if((name != null && r_name == null) || (type != null && r_type == null) || (date != null && r_date == null))
							continue;
						
						//Next if undesired property is in row
						if(name == null && r_name != null)
							continue;
						if(type == null && r_type != null)
							continue;
						if(date == null && r_date != null)
							continue;
						out.println("<tr>");
						
						//Print property
						if(name != null) {
								out.println("<td>"+r_name+"</td>");
						}
						if(type != null) {
								out.println("<td>"+r_type+"</td>");
						}
						if(date != null) {
								out.println("<td>"+r_date.substring(0,10)+"</td>");
						}
						
						out.println("<td>"+count+"</td>");
						
						out.println("</tr>");
						
					}
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
				
				out.println("</table><br />");
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			
			//Display the overall number of records for funsies :D
			String sql = "SELECT COUNT(*) FROM radiology_record";
			
	
			try {
				stmt = conn.createStatement();
				rset = stmt.executeQuery(sql);
				rset.next();
				out.println("TOTAL RECORDS: "+rset.getString(1)+"<br /><br />");
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			
			sql = "";
			
			if(name != null || type != null || date != null) {
				out.println("<h3>Refine Results</h3>");
				out.println("<form action='DataAnalysis' method='GET'>");
			}
				
			
			if(name != null) {
				sql = "SELECT DISTINCT patient_name FROM radiology_record";
				try {
					stmt = conn.createStatement();
					rset = stmt.executeQuery(sql);
					out.println("<h4>Patient Name</h4>");
					out.println("<select name='patient_name'>");
					out.println("<option value='on'>NONE</option>");
					while(rset.next())
						out.println("<option value='"+rset.getString(1)+"'>"+rset.getString(1)+"</option>");
					out.println("</select>");
					stmt.close();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			}
//			else {
//				out.println("<input type='hidden' name='patient_name' value='on' hidden='true' />");
//			}
			if(type != null) {
				sql = "SELECT DISTINCT test_type FROM radiology_record";
				try {
					stmt = conn.createStatement();
					rset = stmt.executeQuery(sql);
					out.println("<h4>Test Type</h4>");
					out.println("<select name='test_type'>");
					out.println("<option value='on'>NONE</option>");
					while(rset.next())
						out.println("<option value='"+rset.getString(1)+"'>"+rset.getString(1)+"</option>");
					out.println("</select>");
					stmt.close();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			}
//			else {
//				out.println("<input type='hidden' name='test_type' value='on' hidden='true' />");
//			}
			if(date != null) {
				out.println("<h4>Test Date Period (YYYY/MM/DD)</h4>");
				out.println("<input type='text' name='test_date' />");
				out.println("<h4>Date Granularity</h4>");
				out.println("<select name='date_granularity'><option value='YYYY'>Years</option><option value='MM'>Months</option><option value='WW'>Weeks</option>");
			}
//			else {
//				out.println("<input type='hidden' name='test_date' value='on' hidden='true' />");
//				out.println("<input type='hidden' name='date_granularity' hidden='true' value='"+granularity+"' />");
//			}
			if(name != null || type != null || date != null) {
				out.println("<br /><br /><br /><input type='submit' value='Refine' />");
				out.println("</form>");
			}
			
			
			out.println("<a href='analysis.jsp'>Back to analysis parameter page.</a>");
			
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			manager.closeConnection();
		}

		out.println(template.getTemplateBottom());
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
	}

}
